package com.thinktank.dao;

import java.sql.*;
import java.util.*;
import com.thinktank.vo.*;
import static com.thinktank.util.DbUtil.*;

public class GuestDao {
	private Connection con = null;

	public GuestDao(Connection con) {
		super();
		this.con = con;
	}
	
	public static final int NPG = 5;
	public int getCount(String field, String query){
		int count = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			String sql = "SELECT COUNT(*) FROM GUEST WHERE "+field+" LIKE ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, "%"+query+"%");
			
			rs = ps.executeQuery();
			
			int temp = 0;
			while(rs.next()){
				temp = rs.getInt(1);
			}
			count = (int)Math.ceil(temp/5.0);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(ps);
		}
		return count;
	}
	
	public List<GuestVo> getAllGuestVos(int page, String field, String query, String url) {
		int start = 1 + NPG * (page-1);
		int end = 5 + NPG * (page-1);
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<GuestVo> list = null;
	
		String sql = "SELECT * FROM "
				+ "(SELECT ROWNUM NUM, TEMP.* FROM "
				+ "(SELECT * FROM GUEST WHERE ("+ field +" LIKE ?) AND (" +
				"GS_MASTER = (SELECT MB_NO FROM MEMBER WHERE MB_PAGE=?)) ORDER BY GS_DATE DESC ) TEMP) "
				+ "WHERE NUM BETWEEN ? AND ?";
	
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, "%" + query + "%");
			ps.setString(2, url);
			ps.setInt(3, start);
			ps.setInt(4, end);
			rs = ps.executeQuery();
			// 선 쿼리 처리 후 목록 불러옴
		
			list = new ArrayList<GuestVo>();
			while(rs.next()) {
				GuestVo guestVo = new GuestVo();
				guestVo.setgNo(rs.getString("gs_no"));
				guestVo.setgMaster(findName(rs.getString("gs_master")));
				guestVo.setgWriter(findName(rs.getString("gs_writer")));
				guestVo.setgContent(rs.getString("gs_content"));
				guestVo.setgDate(rs.getTimestamp("gs_date"));
				
				list.add(guestVo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(ps);
			close(rs);
		}
		return list;
		
	}
	
	public GuestVo getGuestVo(String gNo) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		GuestVo guestVo = null;
		try {
				String sql = "SELECT * FROM GUEST WHERE GS_NO=? ";
				ps = con.prepareStatement(sql);
				ps.setString(1, gNo);
				rs = ps.executeQuery();
	
				if(rs.next()){
					guestVo = new GuestVo();
					guestVo.setgNo(rs.getString("gs_no"));
					guestVo.setgMaster(rs.getString("gs_master"));
					guestVo.setgWriter(rs.getString("gs_writer"));
					guestVo.setgContent(rs.getString("gs_content"));
					guestVo.setgDate(rs.getDate("gs_date"));
				}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(ps);
			close(rs);
		}
			return guestVo;
	}
	
	public int insert(GuestVo guestVo){
		PreparedStatement ps = null;
		int result = 0;
		try {
				// 1. DB 내에서 MAX Gno를 받고 +1을 해서 gno 변수에 세팅
				String temp = "select Max(GS_NO) from GUEST";
				ps = con.prepareStatement(temp);
				ResultSet rs = ps.executeQuery();
				rs.next();

				System.out.println(rs.getInt(1));
				String gNo = rs.getInt(1) + 1 + "";
				for (int i = gNo.length(); i < 3; i++) 
					gNo = "0".concat(gNo);
				close(rs);
	
				// 2. guestVo정보와 Gno로 데이터 삽입
				String sql = "INSERT INTO GUEST VALUES ("
						+ "?, ?, ?, ?, SYSDATE)";
				ps = con.prepareStatement(sql);
				ps.setString(1, gNo);
				ps.setString(2, guestVo.getgMaster());
				ps.setString(3, guestVo.getgWriter());
				ps.setString(4, guestVo.getgContent());
				result = ps.executeUpdate();
				close(ps);
			} catch (Exception e) {
				e.printStackTrace();
			} 
			if(result != 0)		commit(con);
			return result;
		}

	
	public int delete(String gNo){
		PreparedStatement ps = null;
		int result = 0;
		
		try {
			String sql = "DELETE FROM GUEST WHERE GS_NO=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, gNo);
			result = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(ps);
		}
		if(result != 0)		commit(con);
		return result;
	}
		
	public Member getMaster(String url){
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		Member master = null;
		try{
			String sql = "SELECT * FROM MEMBER WHERE MB_PAGE=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, url);
			rs = ps.executeQuery();
			while(rs.next()){
				master = new Member();
				master.setmNo(Integer.parseInt(rs.getString("MB_NO")));
				master.setmPage(rs.getString("MB_PAGE"));
				master.setmName(rs.getString("MB_NAME"));
			}
		} catch (Exception e){
			e.printStackTrace();
		} finally{
			close(ps);
			close(rs);
		}
		return master;
	}
	
	String findName(String mNo){
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String name = null;
		try{
			String sql = "SELECT * FROM MEMBER WHERE MB_NO=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, mNo);
			rs = ps.executeQuery();
			while(rs.next()){
				name = rs.getString("MB_NAME");
			}
		} catch (Exception e){
			e.printStackTrace();
		} finally{
			close(ps);
			close(rs);
		}
		return name;
	}
}
